import pymysql
import xlrd

def excu(sql,p):
    # 1.连接数据库
    con = pymysql.connect(host="localhost",user="root",password="",database="baidu")
    # 2.连接数据台
    sursor = con.cursor()
    # 3.执行
    sursor.executemany(sql,p)
    # 4.提交
    con.commit()
    # 5. 关闭
    sursor.close()
    con.close()


wb = xlrd.open_workbook(filename="5.baidu-员工的人员信息.xls")
st=wb.sheet_by_index(0)
rows = st.nrows
cols = st.ncols


# sql 模板准备
sql = "insert into user_info values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

# 空缓冲列表
p = []


'''
# m满足1000条，加入表中
for i in range(1,rows):
    data = st.row_values(i)

    p.append(data)
    # 如果达到1000条，就插入到数据中
    if len(p) == 4681:
        excu(sql,p) # 调用excu执行批量导入数据
        p.clear()
'''

def find(sql,p,mode="all",size=0):
    # 1.连接数据库
    con = pymysql.connect(host="localhost", user="root", password="", database="baidu")
    # 2.连接数据台
    cursor = con.cursor()
    # 3.执行
    cursor.execute(sql,p)

    if mode=="all":
        return cursor.fetchall()
    elif mode=="one":
        return cursor.fetchone()
    else:
        return cursor.fetchmany(size)
    # 5.提交
    con.commit()
    #6.关闭资源
    cursor.close()
    con.close()


sql1 = r'''select count(*) from user_info where 外包公司  like  '%%传媒%%' '''
sql2 = r'''select count(*) from user_info where 电话号码  like  '133%%' or 电话号码  like  '149%%' or 电话号码  like  '153%%' or 
电话号码  like  '173%%' or 电话号码  like  '177%%' or 电话号码  like  '180%%' or 电话号码  like  '181%%' or 电话号码  like  '189%%' or 
电话号码  like  '1700%%' or 电话号码  like  '1701%%' or 电话号码  like  '1702%%' '''
#sql2 = r'''select count(*) from user_info where 电话号码  like  '188%%'  '''
sql3 = r'''select count(*) from user_info where 性别  in ('女')  '''
sql4 = r'''select count(*) from user_info where 年龄 >='45'   '''
sql5 = r'''select count(*) from user_info where 薪资 <='3000'  '''
sql6 = r'''select count(*) from user_info where 居住地址  like  "黑龙江%%" or 居住地址 like  "北京%%" or 居住地址 like  "福建%%" or 居住地址 like  "四川%%" '''
data = find(sql6,[])
print(data)




